package com.googlecode.flightreports.citydetails;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.PreparedStatement; 
import java.sql.SQLException;
import java.text.SimpleDateFormat;

import com.googlecode.kohmori.reportgen.AbstractSQLSource;
import com.googlecode.kohmori.springmvc.DefaultReportParams;
import org.xml.sax.InputSource;

/* 
 * This class creates the report object and uses SQL to populate its values.
 */
public class ReportSQL extends AbstractSQLSource { 

    /**
     * Creates a report object
     * @param dbConn Connection object to run SQL
     * @return report instance, null if no report found
     */
    public InputSource createReportObject(Connection dbConn, DefaultReportParams reportParams) 
    throws SQLException {
        ReportParams rP = (ReportParams) reportParams;        
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MMM-dd");
        ReportObject rpt = new ReportObject();
        rpt.setQueryParams(rP);

        try {
            // scalars
            String sqlStatement =
                "SELECT country, airport, language, country_iso_code, " +
                " current_timestamp as run_date " +
                " from APP.CITIES" +
                " where city_name = ? ";
            
            PreparedStatement pstmt = dbConn.prepareStatement(sqlStatement);
            pstmt.setString(1, rP.getCityName());
            ResultSet rs = pstmt.executeQuery();

            if (rs.next()) {
                rpt.setRunDate(rs.getTimestamp("run_date"));
                rpt.setCountry(rs.getString("country"));
                rpt.setAirport(rs.getString("airport"));
                rpt.setLanguage(rs.getString("language"));
                rpt.setCountryISOCode(rs.getString("country_iso_code"));
            } else {
                throw new SQLException("No data found for city: " 
                        + rP.getCityName());
            }

            rs.close();
          
            // arrivals
            sqlStatement =
                "SELECT flight_number, " +
                " origin, depart_time," +
                " arrive_time, miles, number_of_segments, " +
                " aircraft_type " +
                " from APP.V_FLIGHTLIST " +
                " where destination_city = ? " +
                " order by arrive_time, flight_number ";
                    
            System.out.println(sqlStatement);
            pstmt = dbConn.prepareStatement(sqlStatement);
            pstmt.setString(1, rP.getCityName());
            rs = pstmt.executeQuery();

            while (rs.next()) {
                ReportObject.ArrivalItem item = new ReportObject.ArrivalItem();
                item.setFlightNumber(rs.getString("flight_number"));
                item.setOrigin(rs.getString("origin"));
                item.setDepartTime(rs.getString("depart_time"));
                item.setArriveTime(rs.getString("arrive_time"));
                item.setMiles(rs.getInt("miles"));
                item.setNumberOfSegments(rs.getInt("number_of_segments"));
                item.setAircraftType(rs.getString("aircraft_type"));
                rpt.addArrivalItem(item);
            }

            pstmt.close();

            // departures
            sqlStatement =
                "SELECT flight_number, " +
                " destination, depart_time," +
                " arrive_time, miles, number_of_segments, " +
                " aircraft_type " +
                " from APP.V_FLIGHTLIST " +
                " where origin_city = ? " +
                " order by depart_time, flight_number ";
                    
            System.out.println(sqlStatement);
            pstmt = dbConn.prepareStatement(sqlStatement);
            pstmt.setString(1, rP.getCityName());
            rs = pstmt.executeQuery();

            while (rs.next()) {
                ReportObject.DepartureItem item = new ReportObject.DepartureItem();
                item.setFlightNumber(rs.getString("flight_number"));
                item.setDestination(rs.getString("destination"));
                item.setDepartTime(rs.getString("depart_time"));
                item.setArriveTime(rs.getString("arrive_time"));
                item.setMiles(rs.getInt("miles"));
                item.setNumberOfSegments(rs.getInt("number_of_segments"));
                item.setAircraftType(rs.getString("aircraft_type"));
                rpt.addDepartureItem(item);
            }

            pstmt.close();

        } catch (SQLException e) {
            throw new SQLException("Exception in ReportSQL: " + e.getMessage());
        }
        return rpt;
    }
}
